package com.test40_JDBC;

import com.test40_JDBC.util.JDBCUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

/**
 * PreparedStatement接口
 */
public class Demo03_PreparedStatement {

    /**
     * 插入操作
     */
    @Test
    public void test1() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "INSERT INTO user(name, age, birthday, salary, note) VALUES (?, ?, ?, ?, ?)";  // 使用占位符
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, "Mr'Lee");
        preparedStatement.setInt(2, 16);
        Date birthday = new Date();
        preparedStatement.setDate(3, new java.sql.Date(birthday.getTime()));
        preparedStatement.setDouble(4, 6900.0);
        preparedStatement.setString(5, "edu.yootk.com");
        int count = preparedStatement.executeUpdate();
        System.out.println("【数据更新】影响的数据行数：" + count);
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 使用setObject()实现占位符的配置处理
     */
    @Test
    public void test2() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "INSERT INTO user(name, age, birthday, salary, note) VALUES (?, ?, ?, ?, ?)";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1, "Mr'Lee");
        preparedStatement.setObject(2, 16);
        Date birthday = new Date();
        preparedStatement.setObject(3, new java.sql.Date(birthday.getTime()));
        preparedStatement.setObject(4, 6900.0);
        preparedStatement.setObject(5, "edu.yootk.com");
        int count = preparedStatement.executeUpdate();
        System.out.println("【数据更新】影响的数据行数：" + count);
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 根据姓名查询
     */
    @Test
    public void test3() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "SELECT uid,name,age, birthday,salary,note FROM user WHERE name=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1, "Mr'Lee");
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.printf("【查询结果】uid = %s、name = %s、age = %s、birthday = %s、salary = %s、note = %s\n",
                    rs.getObject(1), rs.getObject(2), rs.getObject(3), rs.getObject(4), rs.getObject(5), rs.getObject(6));
        }
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 根据ID查询
     */
    @Test
    public void test4() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "SELECT uid,name,age,birthday,salary,note FROM user WHERE uid=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1, 2);
        ResultSet rs = preparedStatement.executeQuery();
        if (rs.next()) {
            System.out.printf("【查询结果】uid = %s、name = %s、age = %s、birthday = %s、salary = %s、note = %s\n",
                    rs.getObject(1), rs.getObject(2), rs.getObject(3), rs.getObject(4), rs.getObject(5), rs.getObject(6));
        }
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 查询user表全部信息
     */
    @Test
    public void test5() throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "SELECT uid,name,age,birthday,salary,note FROM user";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.printf("【查询结果】uid = %s、name = %s、age = %s、birthday = %s、salary = %s、note = %s\n",
                    rs.getObject(1), rs.getObject(2), rs.getObject(3), rs.getObject(4), rs.getObject(5), rs.getObject(6));
        }
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 分页、模糊查询
     */
    @Test
    public void test6() throws Exception {
        int currentPage = 1;
        int lineSize = 2;
        String column = "name";
        String keyword = "李";
        Connection conn = JDBCUtil.getConnection();
        String sql = "SELECT uid,name,age,birthday,salary,note FROM user WHERE " + column + " LIKE ? LIMIT ?, ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1, "%" + keyword + "%");    // 设置模糊查询关键字
        preparedStatement.setObject(2, (currentPage - 1) * lineSize);   // 开始记录行索引
        preparedStatement.setObject(3, lineSize);   // 获取的长度
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.printf("【查询结果】uid = %s、name = %s、age = %s、birthday = %s、salary = %s、note = %s\n",
                    rs.getObject(1), rs.getObject(2), rs.getObject(3), rs.getObject(4), rs.getObject(5), rs.getObject(6));
        }
        JDBCUtil.closeConnection(conn);
    }

    /**
     * 统计相关的数据记录数
     */
    @Test
    public void test7() throws Exception {
        String column = "name";
        String keyword = "李";
        Connection conn = JDBCUtil.getConnection();
        String sql = "SELECT COUNT(*) FROM user WHERE " + column + " LIKE ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1, "%" + keyword + "%");
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            long count = rs.getLong(1);
            System.out.println("满足判断条件的数据行：" + count);
        }
        JDBCUtil.closeConnection(conn);
    }
}
